package cn.casc.tools.er;

import cn.casc.tools.Sortable;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.util.logging.Logger;

/**
 * @author max
 * @version 1.0.1
 * @className Engine
 * @description
 * @copyright Copyright (c) 2023
 * @company 天纲梦工厂
 * @datetime 2024/7/18 14:01
 **/

public class Engine {
    private static Logger LOGGER = Logger.getLogger(Engine.class.getName());
    Map<String, EntityNode> idToEntity;
    SpecialRule rule;

    public Engine(String jdbcUrl, String username, String password) {
        this.idToEntity = new HashMap<>();// getTables(jdbcUrl, username, password);
        this.rule = SpecialRule.load();
        this.buildRelation();
    }

    Map<String, EntityNode> getTables(String jdbcUrl, String username, String password) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(jdbcUrl);
        config.setUsername(username);
        config.setPassword(password);
        config.setDriverClassName("com.mysql.cj.jdbc.Driver");
        HikariDataSource ds = new HikariDataSource(config);
        Map<String, EntityNode> tableToColumn = new HashMap<>();
        try (java.sql.Connection conn = ds.getConnection()) {
            try (Statement stat = conn.createStatement()) {
                ResultSet rs = stat.executeQuery("select * from COLUMNS where TABLE_SCHEMA = 'sdmp_pm'");
                while (rs.next()) {
                    ColumnItem item = new ColumnItem();
                    item.schema = rs.getString("TABLE_SCHEMA");
                    item.table = rs.getString("TABLE_NAME");
                    item.column = rs.getString("COLUMN_NAME");
                    item.columnType = rs.getString("COLUMN_TYPE");
                    item.columnKey = rs.getString("COLUMN_KEY");
                    item.columnComment = rs.getString("COLUMN_COMMENT");

                    // String id = String.format("%s.%s", item.schema, item.table);
                    String id = item.table;
                    EntityNode entityNode = tableToColumn.get(id);
                    if (entityNode == null) {
                        entityNode = new EntityNode(id);
                        tableToColumn.put(id, entityNode);
                    }
                    entityNode.columns.add(item);
                }
            } catch (SQLException e) {
            }
        } catch (SQLException e) {
        }
        return tableToColumn;
    }

    /**
     * 1. A{id, name}, B{id, a_id}, R{a_id,b_id}
     * 2. Biz(biz_type, biz_id)
     * 3. Tree(id, p_id)
     */
    public void buildRelation() {
        for (EntityNode node : idToEntity.values()) {
            for (ColumnItem col : node.columns) {
                List<String> aliases = rule.getAliases(col);
                if (aliases.isEmpty()) {
                    continue;
                }
                for (String alias : aliases) {
                    col.isFK = true;
                    String[] fkCnd = alias.split("@");
                    String ft = fkCnd[0].substring(0, fkCnd[0].length() - 3);
                    EntityNode nodeB = idToEntity.get(ft);
                    if (nodeB != null) {
                        if (fkCnd.length == 1) {
                            col.typeToOther.put("", nodeB);
                            nodeB.referenced.add(col);
                        } else {
                            col.typeToOther.put(fkCnd[1], nodeB);
                            nodeB.referenced.add(col);
                        }
                    } else {
                        List<EntityNode> likes = mostLike(ft, 5);
                        for (EntityNode table : likes) {
                            col.maybe.add(table);
                        }
                    }
                }
            }
        }
    }

    public String exec(String line) {
        if (line.startsWith("\"")) {
            return searchData(line.substring(1));
        } else {
            return search(line);
        }
    }

    public String searchData(String data) {
        return "";
    }

    public List<EntityNode> mostLike(String table, int topN) {
        return mostLike(table, false, topN);
    }

    public List<EntityNode> mostLike(String table, boolean isUndefined, int topN) {
        if ("".equals(table)) {
            topN = Integer.MAX_VALUE;
        }
        List<Sortable<EntityNode>> list = new ArrayList<>();
        Collection<EntityNode> nodes = this.idToEntity.values();
        nodes.forEach((e) -> {
            boolean skip = false;
            if (isUndefined) {
                for (ColumnItem col : e.columns) {
                    if (!col.maybe.isEmpty()) {
                        skip = true;
                    }
                }
            }
            if (!skip) {
                int score = editDistance(table, e.getTable());
                if (!e.getTable().contains(table)) {
                    score += 1000;
                }
                list.add(new Sortable<EntityNode>(score, e));
            }
        });
        Collections.sort(list, (a, b) -> a.score - b.score);
        int cnt = 0;
        List<EntityNode> result = new ArrayList<>();
        for (Sortable<EntityNode> s : list) {
            result.add(s.value);
            if (++cnt > topN) {
                break;
            }
        }
        return result;
    }

    public String search(String from) {
        StringBuilder sb = new StringBuilder();
        Set<String> trace = new HashSet<>();
        sb.append(String.format("SELECT * \nFROM %s\n", from));
        trace.add(from);
        EntityNode fromEntity = this.idToEntity.get(from);
        search(sb, trace, fromEntity);
        return sb.toString();
    }

    public void search(StringBuilder sb, Set<String> trace, EntityNode from) {
        for (ColumnItem item : from.columns) {
            if (item.isFK) {
                for (EntityNode en : item.typeToOther.values()) {
                    if (trace.add(en.id)) {
                        sb.append(String.format("LEFT JOIN %s ON %s.%s = %s.id\n", en.id, item.table, item.column,
                                en.id));
                        search(sb, trace, en);
                    }
                }
            }
        }
        for (ColumnItem item : from.referenced) {
            if (trace.add(item.table)) {
                sb.append(String.format("LEFT JOIN %s ON %s.%s = %s.id\n", item.table, item.table, item.column, from.id));
                EntityNode en = this.idToEntity.get(item.table);
                search(sb, trace, en);
            }
        }
    }

    public static int editDistance(String s1, String s2) {
        int L1 = s1.length();
        int L2 = s2.length();
        int[][] dp = new int[L1 + 1][L2 + 1];
        for (int i = 0; i < s1.length(); i++) {
            dp[i + 1][0] = 1 + i;
        }
        for (int i = 0; i < s2.length(); i++) {
            dp[0][i + 1] = 1 + i;
        }
        for (int i = 0; i < L1; i++) {
            for (int j = 0; j < L2; j++) {
                if (s1.charAt(i) == s2.charAt(j)) {
                    dp[i + 1][j + 1] = dp[i][j];
                } else {
                    dp[i + 1][j + 1] = Math.min(dp[i][j], Math.min(dp[i + 1][j], dp[i][j + 1])) + 1;
                }
            }
        }

        return dp[L1][L2];
    }

}